-- 明细
with waybill_scan as (
-- 长宽高重,取15天且是始发转运中心之前的扫描记录,取出最大的长宽高和重量
select 
    scan.waybill_no
    ,split(max(package_dimensions_msg), '#')[2] as package_length
    ,split(max(package_dimensions_msg), '#')[3] as package_wide 
    ,split(max(package_dimensions_msg), '#')[4] as package_high
    ,max(double(package_weight))                as waybill_weight
from (
select 
    waybill_no
    ,scan_time
    ,length as package_length
    ,width  as package_wide 
    ,height as package_high
    ,weight as package_weight
    ,concat(
        lpad(nvl(length ,0) * nvl(width ,0) * nvl(height ,0), 10,'0'), '#',  scan_time, '#', nvl(length ,0), '#', nvl(width ,0), '#', nvl(height ,0), '#', nvl(weight, 0)
    )   as   package_dimensions_msg   
from 
    jms_dwd.dwd_tab_barscan_taking_base_dt
where 
    dt between date_sub('{{ execution_date | cst_ds }}', 14) and '{{ execution_date | cst_ds }}'
    and !(length(waybill_no) = 13 and waybill_no like 'B%')

union all 

select 
    waybill_no
    ,scan_time
    ,length as package_length
    ,width  as package_wide 
    ,height as package_high
    ,weight as package_weight
    ,concat(
        lpad(nvl(length ,0) * nvl(width ,0) * nvl(height ,0), 10,'0'), '#',  scan_time, '#', nvl(length ,0), '#', nvl(width ,0), '#', nvl(height ,0), '#', nvl(weight, 0)
    )   as   package_dimensions_msg     
from 
    jms_dwd.dwd_tab_barscan_warehousing_base_dt
where 
    dt between date_sub('{{ execution_date | cst_ds }}', 14) and '{{ execution_date | cst_ds }}'
    and !(length(waybill_no) = 13 and waybill_no like 'B%')


union all 

select 
    waybill_no
    ,scan_time
    ,length as package_length
    ,width  as package_wide 
    ,height as package_high
    ,weight as package_weight
    ,concat(
        lpad(nvl(length ,0) * nvl(width ,0) * nvl(height ,0), 10,'0'), '#',  scan_time, '#', nvl(length ,0), '#', nvl(width ,0), '#', nvl(height ,0), '#', nvl(weight, 0)
    )   as   package_dimensions_msg     
from 
    jms_dwd.dwd_tab_barscan_arrival_base_dt
where 
    dt between date_sub('{{ execution_date | cst_ds }}', 14) and '{{ execution_date | cst_ds }}'
    and !(length(waybill_no) = 13 and waybill_no like 'B%')

) scan
    join ( -- 取始发中心最小发车时间或最小到件时间中较大的一个: greatest(min(arrival_scan_time), min(send_scan_time)), 无法防止发车漏扫且到件多扫的情况
            select 
                send_arrival_scan.waybill_no                                       -- 运单
                ,if(
                    nvl(min(send_arrival_scan.arrival_scan_time),'0000-00-00 00:00:00') >= nvl(min(send_arrival_scan.send_scan_time),'0000-00-00 00:00:00')
                    , min(send_arrival_scan.arrival_scan_time)
                    , min(send_arrival_scan.send_scan_time)
                )
                    as first_center_latest_scan_time -- 首中心最晚扫描时间(到件扫描最小值和发件扫描最小值中取较大的时间, 防止后面环节补扫)
            from(
                  select waybill_no
                       , scan_site_code
                       , scan_time as arrival_scan_time
                       , null      as send_scan_time
                  from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
                  where dt between date_sub('{{ execution_date | cst_ds }}', 14) and '{{ execution_date | cst_ds }}'
                    and !(length(waybill_no) = 13 and waybill_no like 'B%')

                  union all

                  select waybill_no
                       , scan_site_code
                       , null      as arrival_scan_time
                       , scan_time as send_scan_time
                  from jms_dwd.dwd_tab_barscan_centersend_base_dt
                  where dt between date_sub('{{ execution_date | cst_ds }}', 14) and '{{ execution_date | cst_ds }}'
                    and !(length(waybill_no) = 13 and waybill_no like 'B%')

            ) send_arrival_scan
                join jms_dwd.dwd_wide_sign_summary_waybill_dt sing_waybill
                        on send_arrival_scan.waybill_no = sing_waybill.waybill_no
                               and sing_waybill.dt = '{{ execution_date | cst_ds }}'
                               and send_arrival_scan.scan_site_code = sing_waybill.first_center_network_code
            group by 
                send_arrival_scan.waybill_no
    ) first_center_scan
        on scan.waybill_no = first_center_scan.waybill_no
               and substr(scan.scan_time,1,19) <= substr(first_center_scan.first_center_latest_scan_time,1, 19)
    group by scan.waybill_no
)
,bagging_detail as (
    select 
        waybill_no
        ,scan_site_code
    from 
        jms_dwd.dwd_tab_barscan_bagging_base_dt a
    where 
        dt between date_sub('{{ execution_date | cst_ds }}', 15) and '{{ execution_date | cst_ds }}'
    group by 
         waybill_no
        ,scan_site_code
)
,dim_network as (
    select 
        code
        ,name
        ,network_type
        ,agent_code
        ,agent_name
        ,virt_code
        ,virt_name
        ,in_entrepot_code_new
        ,in_entrepot_name_new
        ,in_distribution_code_new
        ,in_distribution_name_new
    from jms_dim.dim_network_whole_massage
    where is_enable=1
    and   is_delete=1
),
network_distribute_center as ( -- 得到网点的直接和间接的可交货中心
    select
        network_distribute.code
        ,network_distribute.name
        ,concat_ws(',',collect_list(concat_ws(',',network_distribute.in_distribution_code_new,',',nodal_distribute.in_distribution_code_new))) as network_distribute_center_code
    from (
        select a.code
               , a.name
               , tmp.distribute_nodal
               , a.in_distribution_code_new
        from   jms_dim.dim_network_whole_massage a
                   lateral view explode(split(in_entrepot_code_new, ',')) tmp as distribute_nodal
        where  network_type = '6'
    ) network_distribute left join jms_dim.dim_network_whole_massage nodal_distribute on network_distribute.distribute_nodal = nodal_distribute.code
    where distribute_nodal is not null
    group by
        network_distribute.code,network_distribute.name
)
,detail as (
    select
        a.waybill_no
        , a.sign_date
        , a.pick_network_code
        , a.pick_network_name
        , a.pick_agent_code
        , a.pick_agent_name
        , a.pick_virt_code
        , a.pick_virt_name
        , a.first_nodal_code
        , a.first_nodal_name
        , a.first_center_code
        , a.first_center_name
        , a.end_center_code
        , a.end_center_name
        , a.end_center_agent_code
        , a.end_center_agent_name
        , a.end_center_virt_code
        , a.end_center_virt_name
        , a.customer_code
        , a.customer_name
        , a.goods_type_code
        , a.goods_type_name
        , a.waybill_weight
        , a.should_bagging_site_code
        , dim_network10.name           as should_bagging_site_name
        , dim_network10.network_type   as should_bagging_site_type
        , dim_network10.agent_code     as should_bagging_site_agent_code
        , dim_network10.agent_name     as should_bagging_site_agent_name
        , dim_network10.virt_code      as should_bagging_site_virt_code
        , dim_network10.virt_name      as should_bagging_site_virt_name
        -- 实建包站点 优先取首中心已建包的, 次取第一个建包站点
        ,case
            when bagging_detail.waybill_no is not null then a.should_bagging_site_code
            else a.first_bagging_site_code
        end                                       as actual_bagging_site_code -- 实建包站点
        ,case
            when bagging_detail.waybill_no is not null then dim_network10.name
            else first_bagging_site_name
        end                                       as actual_bagging_site_name -- 实建包站点
        ,case
            when bagging_detail.waybill_no is not null then dim_network10.network_type
            else first_bagging_site_type
        end                                       as actual_bagging_site_type -- 实建包站点类型 网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散,6-网点
        ,case
            when bagging_detail.waybill_no is not null then dim_network10.agent_code
            else first_bagging_site_agent_code
        end                                       as actual_bagging_site_agent_code
        ,case
            when bagging_detail.waybill_no is not null then dim_network10.agent_name
            else first_bagging_site_agent_name
        end                                       as actual_bagging_site_agent_name
        ,case
            when bagging_detail.waybill_no is not null then dim_network10.virt_code
            else first_bagging_site_virt_code
        end                                       as actual_bagging_site_virt_code
        ,case
            when bagging_detail.waybill_no is not null then dim_network10.virt_name
            else first_bagging_site_virt_name
        end                                       as actual_bagging_site_virt_name
        , a.package_dimensions
        , a.transfer_site
    from(
        select
             wide.waybill_no
            ,wide.dt                             as sign_date
            ,wide.pick_network_code
            ,dim_network1.name as pick_network_name
            ,dim_network1.agent_code             as pick_agent_code
            ,dim_network1.agent_name             as pick_agent_name
            ,dim_network1.virt_code              as pick_virt_code
            ,dim_network1.virt_name              as pick_virt_name
            ,wide.first_nodal_network_code       as first_nodal_code     --始发集散code
            ,dim_network2.name                   as first_nodal_name     --始发集散名称
            ,wide.first_center_network_code      as first_center_code    --始发转运中心
            ,dim_network3.name                   as first_center_name    --始发转运名称
            ,wide.end_center_network_code        as end_center_code      --末端中心名称
            ,dim_network4.name                   as end_center_name      --末端中心名称
            ,dim_network4.agent_code             as end_center_agent_code--末端中心代理区
            ,dim_network4.agent_name             as end_center_agent_name--末端中心代理区
            ,dim_network4.virt_code              as end_center_virt_code --末端中心虚拟代理区
            ,dim_network4.virt_name              as end_center_virt_name --末端中心虚拟代理区
            ,wide.customer_code                                          --客户编码
            ,wide.customer_name                                          --客户名称
            ,wide.goods_type_code                                        --物品类型code
            ,wide.goods_type_name                                        --物品类型name
            ,waybill_scan.waybill_weight                                 --重量
            ,wide.bagging_early_scansitecode     as first_bagging_site_code -- 首建包站点code
            ,dim_network5.name                   as first_bagging_site_name -- 首建包站点名称
            ,dim_network5.network_type           as first_bagging_site_type -- 首建包站点类型
            ,dim_network5.agent_code             as first_bagging_site_agent_code -- 首建包站点代理区
            ,dim_network5.agent_name             as first_bagging_site_agent_name -- 首建包站点代理区
            ,dim_network5.virt_code              as first_bagging_site_virt_code  -- 首建包站点虚拟代理区
            ,dim_network5.virt_name              as first_bagging_site_virt_name  -- 首建包站点虚拟代理区
            ,case -- 判断应建包站点
                -- 1. 网点-集散-中心,如果集散中心有交派关系,中心应建包
                when wide.first_nodal_network_code is not null
                     and wide.first_center_network_code is not null
                     -- and array_contains(split(dim_network1.in_entrepot_code_new, ','), wide.first_nodal_network_code)
                     and array_contains(split(dim_network2.in_distribution_code_new, ','), wide.first_center_network_code) then wide.first_center_network_code
                -- 2. 网点-集散-中心,如果网点集散有交派关系,集散中心没有交派关系,集散应建包
                when wide.first_nodal_network_code is not null
                     and wide.first_center_network_code is not null
                     and array_contains(split(dim_network1.in_entrepot_code_new, ','), wide.first_nodal_network_code)
                     and not array_contains(split(dim_network2.in_distribution_code_new, ','), wide.first_center_network_code) then wide.first_nodal_network_code
                -- 3. 网点-中心轨迹,如果网点中心有直接或间接交派关系,中心应建包
                when wide.first_nodal_network_code is null
                         and wide.first_center_network_code is not null
                         and (array_contains(split(dim_network1.in_distribution_code_new, ','), wide.first_center_network_code)
                              or array_contains(split(network_distribute_center.network_distribute_center_code, ','), wide.first_center_network_code))
                             then wide.first_center_network_code
                else wide.pick_network_code
            end                                       as should_bagging_site_code  -- 应建包站点
            ,concat(
                package_length, ',', package_wide ,',', package_high
            )                                         as package_dimensions
            ,replace(
                concat_ws('-'
                    , dim_network3.name   --始发转运名称
                    , if(dim_network6.name = dim_network3.name, null,  dim_network6.name)   --第二转运名称
                    , if(dim_network7.name = dim_network6.name, null,  dim_network7.name)   --第三转运名称
                    , if(dim_network8.name = dim_network7.name, null,  dim_network8.name)   --第四转运名称
                    , if(dim_network9.name = dim_network8.name, null,  dim_network9.name)   --第五转运名称
                    , if(
                            coalesce(fifth_center_network_code, fourth_center_network_code, third_center_network_code, second_center_network_code, first_center_network_code) = end_center_network_code
                            -- array_contains(array(first_center_network_code, second_center_network_code, third_center_network_code, fourth_center_network_code, fifth_center_network_code), end_center_network_code)
                            , null
                            ,dim_network4.name
                        )   -- 末端转运名称
                )
            ,'转运中心','')                                      as  transfer_site
        from( -- 只保留出港首集散
            select
                waybill_no
                , dt
                , taking_early_scansitecode as pick_network_code
                -- 首集散可能是末端集散,剔掉
                , case when
                           coalesce(first_nodal_arrival_early_time,first_nodal_unloading_early_time,first_nodal_loading_later_time,first_nodal_send_later_time)
                           > coalesce(first_center_unloading_early_time, first_center_arrival_early_time,first_center_loading_later_time,first_center_send_later_time) then null
                       else first_nodal_network_code
                  end as first_nodal_network_code
                , first_center_network_code
                , second_center_network_code
                , third_center_network_code
                , fourth_center_network_code
                , fifth_center_network_code
                , end_center_network_code
                , customer_code
                , customer_name
                , goods_type_code
                , goods_type_name
                , bagging_early_scansitecode
                , is_void
                , is_reback
                , is_abnormal
            from
                jms_dwd.dwd_wide_sign_summary_waybill_dt
            where
                dt = '{{ execution_date | cst_ds }}'
                and is_void = 0
                and is_reback = 0      -- 转寄剔除
                and is_abnormal = 0    -- 问题件剔除
                and first_center_network_code <> end_center_network_code -- 去除始发中心下一站是网点/集散点的数据统计
                and pick_network_code <> '888888'
                and pick_network_name is not null -- 去掉网点code不合法的数据(可能是百世网点)
            ) wide
        left join
            dim_network dim_network1
                on wide.pick_network_code = dim_network1.code
        left join
            dim_network dim_network2
                on wide.first_nodal_network_code = dim_network2.code
        left join
            dim_network dim_network3
                on wide.first_center_network_code = dim_network3.code
        left join
            dim_network dim_network4
                on wide.end_center_network_code = dim_network4.code
        left join
            dim_network dim_network5
                on wide.bagging_early_scansitecode = dim_network5.code
        ---------------------
        -- 第二到第五转运中心
        left join
            dim_network dim_network6
                on wide.second_center_network_code = dim_network6.code
        left join
            dim_network dim_network7
                on wide.third_center_network_code = dim_network7.code
        left join
            dim_network dim_network8
                on wide.fourth_center_network_code = dim_network8.code
        left join
            dim_network dim_network9
                on wide.fifth_center_network_code = dim_network9.code
        ---------------------
        left join
            network_distribute_center -- 获取网点间接可交货转运中心
                on wide.pick_network_code = network_distribute_center.code
        left join
            waybill_scan
                on wide.waybill_no = waybill_scan.waybill_no
        left join (
            select
                a.customer_code
                ,a.customer_name
            from
                jms_dim.dim_problem_express_wo_record_base a
                    left join jms_dim.dim_pew_record_type_base b
                        on a.record_type_id = b.id
            where
                -- a.record_type_id = '573074171379081224'
                b.type_code = '66'
                and a.record_effect_status = '1'
                and a.record_status = '3'
                and a.archive_date <= '{{ execution_date | cst_ds }}'
            group by
                a.customer_code, a.customer_name
        ) customer_filter
                on wide.customer_code = customer_filter.customer_code
        where
            -- 类型必须强转, 隐式转换不能正确过滤
            pick_network_code is not null
            and double(waybill_scan.waybill_weight) <= 3
            and double(waybill_scan.package_length) <= 50
            and double(waybill_scan.package_wide)   <= 50
            and double(waybill_scan.package_high)   <= 50
            and double(package_length + package_wide + package_high) <= 90
            and customer_filter.customer_code is null -- 备案客户剔除
        ) a
        -- 应建包站点是否建包
        left join
            bagging_detail
                on a.waybill_no = bagging_detail.waybill_no and a.should_bagging_site_code = bagging_detail.scan_site_code
        left join dim_network dim_network10
                on a.should_bagging_site_code = dim_network10.code
    )
insert overwrite table jms_dm.dm_outport_bagging_fine_detail_dt partition (dt = '{{ execution_date | cst_ds }}')
select  
    waybill_no   
    , sign_date                  
    , pick_network_code            
    , pick_network_name            
    , pick_agent_code              
    , pick_agent_name              
    , pick_virt_code               
    , pick_virt_name               
    , first_nodal_code             
    , first_nodal_name             
    , first_center_code            
    , first_center_name            
    , end_center_code              
    , end_center_name              
    , customer_code                
    , customer_name                
    , goods_type_code              
    , goods_type_name              
    , waybill_weight               
    , should_bagging_site_code     
    , should_bagging_site_name     
    , should_bagging_site_type     
    , actual_bagging_site_code     
    , actual_bagging_site_name     
    , actual_bagging_site_type     
    , transfer_site                
    , package_dimensions           
    , unbagging_fine_flag          
    , backfill_bagging_reward_flag 
    , post_operate_perk
    , should_bagging_site_agent_code -- 应建包站点代理区
    , should_bagging_site_agent_name -- 应建包站点代理区
    , should_bagging_site_virt_code  -- 应建包站点虚拟代理区
    , should_bagging_site_virt_name  -- 应建包站点虚拟代理区
    , actual_bagging_site_agent_code -- 实建包站点代理区
    , actual_bagging_site_agent_name -- 实建包站点代理区
    , actual_bagging_site_virt_code  -- 实建包站点虚拟代理区
    , actual_bagging_site_virt_name  -- 实建包站点虚拟代理区
    , end_center_agent_code          -- 末端中心代理区
    , end_center_agent_name          -- 末端中心代理区
    , end_center_virt_code           -- 末端中心虚拟代理区
    , end_center_virt_name           -- 末端中心虚拟代理区
from (
    select 
        waybill_no
        , sign_date
        , pick_network_code
        , pick_network_name
        , pick_agent_code
        , pick_agent_name
        , pick_virt_code
        , pick_virt_name
        , first_nodal_code 
        , first_nodal_name 
        , first_center_code
        , first_center_name
        , end_center_code
        , end_center_name
        , customer_code  
        , customer_name  
        , goods_type_code
        , goods_type_name
        , waybill_weight 
        , should_bagging_site_code
        , should_bagging_site_name
        , should_bagging_site_type
        , actual_bagging_site_code
        , actual_bagging_site_name
        , actual_bagging_site_type
        , transfer_site
        , package_dimensions
        , case 
            when should_bagging_site_type = '4' and actual_bagging_site_type in ('5', '6') then 0   -- 中心应建, 但网点集散提前建好
            when should_bagging_site_code <> nvl(actual_bagging_site_code, '0')  then 1   
            else 0 
        end as unbagging_fine_flag            -- 未建包罚款标识
        , case 
            when should_bagging_site_type = '4' and actual_bagging_site_type in ('5', '6') then 0
            when should_bagging_site_code <> actual_bagging_site_code then 1 
            else 0 
        end as backfill_bagging_reward_flag   -- 补建包奖励标识
        , case when should_bagging_site_code is not null and actual_bagging_site_code is null then 1 else 0 end as post_operate_perk   -- 小件操作补贴标识
        , case when should_bagging_site_code = actual_bagging_site_code then 1 else 0 end as normal_bagging_flag
        , should_bagging_site_agent_code -- 应建包站点代理区
        , should_bagging_site_agent_name -- 应建包站点代理区
        , should_bagging_site_virt_code  -- 应建包站点虚拟代理区
        , should_bagging_site_virt_name  -- 应建包站点虚拟代理区
        , actual_bagging_site_agent_code -- 实建包站点代理区
        , actual_bagging_site_agent_name -- 实建包站点代理区
        , actual_bagging_site_virt_code  -- 实建包站点虚拟代理区
        , actual_bagging_site_virt_name  -- 实建包站点虚拟代理区
        , end_center_agent_code          -- 末端中心代理区
        , end_center_agent_name          -- 末端中心代理区
        , end_center_virt_code           -- 末端中心虚拟代理区
        , end_center_virt_name           -- 末端中心虚拟代理区
    from 
        detail
    where
        actual_bagging_site_code is null
        -- 剔除包进包出包号不变
        or actual_bagging_site_type <= should_bagging_site_type
) a 
where 
    unbagging_fine_flag + backfill_bagging_reward_flag + post_operate_perk + normal_bagging_flag > 0
    and not( actual_bagging_site_type in('6', '5') and backfill_bagging_reward_flag = 1) -- 剔除奖励集散的单号
distribute by pmod(hash(rand()), 10)
;
